|
RAIS
3.2
|
00001 00028 using System; 00029 using System.Collections; 00030 using System.Collections.Generic; 00031 using System.Data; 00032 using System.Data.SqlClient; 00033 using System.Text; 00034 using System.Transactions; 00035 using System.Web; 00036 using System.Globalization; 00037 00038 using RAIS.Common.DynamicMaskManagement; 00039 using RAIS.Common.MessageManagement; 00040 using RAIS.Common.TableManagement; 00041 using RAIS.Common.UserManagement; 00042 00043 using DynamicMaskTypes = System.Collections.Generic.Dictionary<RAIS.Common.DynamicMaskManagement.DynamicMask.DynamicMaskType, RAIS.Common.DynamicMaskManagement.DynamicMask.DynamicMaskTypeData>; 00044 using Users = System.Collections.Generic.Dictionary<string, RAIS.Common.UserManagement.UserData>; 00045 using RecordTypes = System.Collections.Generic.Dictionary<string, RAIS.Common.DynamicMaskManagement.ConsistencyCheck.RecordTypeData>; 00046 00047 namespace RAIS.DataAccessLayer 00048 { 00052 public class DynamicMaskManagement 00053 { 00054 public static int SelectedFacility; 00055 public static int SelectedDepartment; 00056 //********************************************************************* 00057 #region Enums 00058 public enum SelectType 00059 { 00060 MainSelect = 0, 00061 ForInsert, 00062 Validation, 00063 ValidationForInsert 00064 } 00065 public enum MultilookupResetType 00066 { 00067 NotReset = 0, 00068 ResetSource, 00069 ResetAll 00070 } 00071 #endregion 00072 //********************************************************************* 00073 #region Constructors 00074 /* 00075 \brief Static class constructor - fills DynamicMaskTypesDictionary property 00076 of DynamicMask class 00077 */ 00078 static DynamicMaskManagement() 00079 { 00080 DynamicMask.DynamicMaskTypesDictionary = GetDynamicMaskTypes(); 00081 ConsistencyCheck.RecordTypesDictionary = GetRecordTypes(); 00082 } 00083 #endregion 00084 //********************************************************************* 00085 #region Public Methods 00086 00095 public static DynamicMaskData GetDynamicMaskData(int maskID, int tabID, int parentID, UserData currentUser, bool isNewRow) 00096 { 00097 DynamicMaskData dynamicMaskData = new DynamicMaskData(); 00098 00099 // Set DynamicMask properties values from params 00100 dynamicMaskData.ParentID = parentID; 00101 dynamicMaskData.TabID = tabID; 00102 dynamicMaskData.CurrentUser = currentUser; 00103 dynamicMaskData.IsNewRow = isNewRow; 00104 dynamicMaskData.DynamicMasks = GetDynamicMasks(maskID); 00105 00106 // Get DynamicMask properties values from DB 00107 GetDynamicMaskDataProperties(dynamicMaskData); 00108 00109 return dynamicMaskData; 00110 } 00118 public static void GetDynamicMaskDataProperties(DynamicMaskData dynamicMaskData) 00119 { 00120 if (dynamicMaskData.DynamicMask.Type == DynamicMask.DynamicMaskType.SingleRowMaskWithList) 00121 { 00122 GetSingleListFieldValues(dynamicMaskData); 00123 } 00124 00125 if (dynamicMaskData.DynamicMask.Type == DynamicMask.DynamicMaskType.ListWithFilter 00126 || dynamicMaskData.DynamicMask.Type == DynamicMask.DynamicMaskType.ReportMask) 00127 { 00128 dynamicMaskData.FiltersData = GetDynamicMaskFiltersData(dynamicMaskData); 00129 } 00130 } 00137 public static void GetSingleListFieldValues(DynamicMaskData dynamicMaskData) 00138 { 00139 //if (dynamicMaskData.ParentID == 0) 00140 if (DataRoleRestrictionManagement.IsDataRoleRestricted(dynamicMaskData)) 00141 { 00142 // Apply Data Role Restrictions 00143 if (!dynamicMaskData.IsMultipaged) 00144 DataRoleRestrictionManagement.ApplyDataRoleRestrictions(dynamicMaskData, 0, "", 0); 00145 else 00146 DataRoleRestrictionManagement.ApplyDataRoleRestrictions(dynamicMaskData, 0, dynamicMaskData.DynamicMask.Table.GetForeignKeyFieldNameByTableID(dynamicMaskData.DynamicMasks[0].Table.ID), (dynamicMaskData.ParentID == 0) ? -1 : dynamicMaskData.ParentID); 00147 } 00148 else 00149 { 00150 if (!dynamicMaskData.IsMultipaged) 00151 dynamicMaskData.SingleListFieldValues = 00152 GetAllNameFieldValues(dynamicMaskData.DynamicMask.Table, dynamicMaskData.DynamicMask.QueryName); 00153 else 00154 dynamicMaskData.SingleListFieldValues = 00155 GetNameFieldValuesWithFilter(dynamicMaskData.DynamicMask.Table 00156 , dynamicMaskData.DynamicMask.QueryName 00157 , dynamicMaskData.DynamicMask.Table.GetForeignKeyFieldNameByTableID( 00158 dynamicMaskData.DynamicMasks[0].Table.ID) 00159 , (dynamicMaskData.ParentID == 0) ? -1 : dynamicMaskData.ParentID); 00160 } 00161 } 00170 public static List<FieldData> GetCurrentRowFieldsValues(DynamicMaskData dynamicMaskData, int parentID, int rowId) 00171 { 00172 Table table = dynamicMaskData.DynamicMask.Table; 00173 00174 List<FieldData> fieldsValues = new List<FieldData>(); 00175 00176 string[] sqlStrings = GetSelectStrings(table 00177 , dynamicMaskData.DynamicMasks[0].Table.ID 00178 , parentID 00179 , rowId 00180 , dynamicMaskData.IsNewRow); 00181 00182 dynamicMaskData.DataAdapter = 00183 GetDataAdapter(sqlStrings[(int)SelectType.MainSelect] 00184 , sqlStrings[(int)SelectType.ForInsert] 00185 , dynamicMaskData.DataSet); 00186 00187 if (dynamicMaskData.DynamicMask.Table.NeedsValidation) 00188 { 00189 dynamicMaskData.ValidationDataAdapter = 00190 GetDataAdapter(sqlStrings[(int)SelectType.Validation] 00191 , sqlStrings[(int)SelectType.ValidationForInsert] 00192 , dynamicMaskData.ValidationDataSet); 00193 } 00194 00195 if (dynamicMaskData.DataSet.Tables[0].Rows.Count != 0) 00196 { 00197 DataRow row = dynamicMaskData.DataSet.Tables[0].Rows[0]; 00198 00199 if (dynamicMaskData.DataSet.Tables[0].Rows.Count == 2) 00200 { 00201 dynamicMaskData.DataSet.Tables[0].Rows.Remove(row); 00202 row = dynamicMaskData.DataSet.Tables[0].Rows[0]; 00203 } 00204 00205 fieldsValues = FillFieldDataListFromTableRow(table, row, false, dynamicMaskData.CurrentUser.ID); 00206 00207 dynamicMaskData.Protector = GetProtector(table.Name, rowId); 00208 } 00209 else 00210 { 00211 fieldsValues = FillFieldDataListFromTableRow(table, null, false, dynamicMaskData.CurrentUser.ID); 00212 } 00213 00214 return fieldsValues; 00215 } 00222 public static bool GetLookupValues(DynamicMaskData dynamicMaskData, ref string returnMessage) 00223 { 00224 SelectedDepartment = dynamicMaskData.SelectedDepartmentID; 00225 SelectedFacility = dynamicMaskData.SelectedFacilityID; 00226 00227 foreach (FieldData fieldData in dynamicMaskData.CurrentRowValues) 00228 { 00229 if (fieldData.Field.Type == Field.FieldType.SingleLookup 00230 || fieldData.Field.Type == Field.FieldType.LookupPlusIntegerValue 00231 || fieldData.Field.Type == Field.FieldType.LookupPlusScientificValue 00232 || fieldData.Field.Type == Field.FieldType.MultipleLookup 00233 00234 || (fieldData.Field.QueryName != string.Empty 00235 //&& fieldData.Value == string.Empty 00236 ) 00237 ) 00238 { 00239 Field currentField = fieldData.Field; 00240 //bool resetMultilookup = false; 00241 MultilookupResetType resetMultilookup = MultilookupResetType.NotReset; 00242 string queryName = ReplaceParamsInQueryName(currentField.QueryName, dynamicMaskData.CurrentRowValues, dynamicMaskData.SelectedFacilityID, fieldData, dynamicMaskData.CurrentUser.ID, 0, ref resetMultilookup); 00243 00244 if (fieldData.Field.Type == Field.FieldType.MultipleLookup) 00245 { 00246 // Multilookup fields 00247 Table table = dynamicMaskData.DynamicMask.Table; 00248 DataRow row = (dynamicMaskData.DataSet.Tables[0].Rows.Count == 2) 00249 ? dynamicMaskData.DataSet.Tables[0].Rows[1] 00250 : dynamicMaskData.DataSet.Tables[0].Rows[0]; 00251 00252 if (resetMultilookup != MultilookupResetType.NotReset) 00253 { 00254 00255 if (resetMultilookup == MultilookupResetType.ResetAll 00256 || resetMultilookup == MultilookupResetType.ResetSource) 00257 fieldData.SavedVisibleLookupValues = null; 00258 if (resetMultilookup == MultilookupResetType.ResetAll) 00259 fieldData.SavedVisibleSelectedMultiLookupValues = null; 00260 } 00261 fieldData.LookupValues = GetNotSelectedMultipleLookupValues(table, queryName, row, currentField, false); 00262 } 00263 else 00264 { 00265 if (fieldData.Field.IsLookupField) 00266 { 00267 // Lookup fields 00268 Dictionary<int, string> val = GetAllNameFieldValues(fieldData.Field.RelatedTable, queryName); 00269 fieldData.LookupValues = val; 00270 if (val == null) 00271 { 00272 returnMessage = "Primary key in query don't equals with primary key in table."; 00273 return false; 00274 } 00275 00276 if (!fieldData.LookupValues.ContainsKey(fieldData.CurrentLookupValue)) 00277 { 00278 fieldData.CurrentLookupValue = 0; 00279 fieldData.CurrentLookupText = string.Empty; 00280 } 00281 } 00282 else 00283 { 00284 // Textboxes 00285 if (fieldData.Value == string.Empty || (dynamicMaskData.IsNewRow && queryName.StartsWith("EXEC "))) 00286 fieldData.Value = GetNotLookupFieldValueFromQuery(queryName); 00287 } 00288 } 00289 } 00290 00291 } 00292 return true; 00293 } 00299 public static void CreateNewRecord(DynamicMaskData dynamicMaskData) 00300 { 00301 dynamicMaskData.DataSet.Tables[0].Rows.Add(); 00302 dynamicMaskData.CurrentRowValues = FillFieldDataListFromTableRow( 00303 dynamicMaskData.DynamicMask.Table 00304 , dynamicMaskData.DataSet.Tables[0].Rows[dynamicMaskData.DataSet.Tables[0].Rows.Count - 1] 00305 , false 00306 , dynamicMaskData.CurrentUser.ID); 00307 00308 if (dynamicMaskData.ParentID != 0) 00309 foreach (FieldData fieldData in dynamicMaskData.CurrentRowValues) 00310 if (fieldData.Field.ForeignKeyFieldName == 00311 dynamicMaskData.DynamicMask.Table.GetForeignKeyFieldNameByTableID(dynamicMaskData.DynamicMasks[0].Table.ID)) 00312 { 00313 fieldData.Value = dynamicMaskData.ParentID.ToString(); 00314 fieldData.CurrentLookupValue = dynamicMaskData.ParentID; 00315 break; 00316 } 00317 } 00323 public static bool SaveChanges(DynamicMaskData dynamicMaskData, Message.ModificationType modificationType, Message.ModificationType mainMaskModificationType, ref string returnMessage) 00324 { 00325 try 00326 { 00327 LoggingSystem parentLog = null; 00328 if (dynamicMaskData.ParentDynamicMaskData != null) 00329 { 00330 parentLog = new LoggingSystem(dynamicMaskData.ParentDynamicMaskData); 00331 parentLog.Open(); 00332 } 00333 LoggingSystem log = new LoggingSystem(dynamicMaskData); 00334 log.Open(); 00335 00336 // Fill DataSets rows from FieldData values 00337 if (dynamicMaskData.ParentDynamicMaskData != null) 00338 FillRowsFromFieldsValues(dynamicMaskData.ParentDynamicMaskData, modificationType, parentLog); 00339 FillRowsFromFieldsValues(dynamicMaskData, modificationType, log); 00340 00341 // Always use TransactionScope becase Validation needs TransactionScope to save 00342 // changes and message as one transaction 00343 using (TransactionScope ts = new TransactionScope()) 00344 { 00345 if (dynamicMaskData.ParentDynamicMaskData != null) 00346 { 00347 CallDataAdapterUpdate(dynamicMaskData.ParentDynamicMaskData, modificationType, mainMaskModificationType); 00348 00349 dynamicMaskData.CurrentRow[dynamicMaskData.ParentDynamicMaskData.DynamicMask.Table.ForeignKeyFieldName] 00350 = dynamicMaskData.ParentDynamicMaskData.CurrentRowKeyValue; 00351 dynamicMaskData.ParentID = dynamicMaskData.ParentDynamicMaskData.ParentID; 00352 dynamicMaskData.ValidationMessageID = dynamicMaskData.ParentDynamicMaskData.ValidationMessageID; 00353 00354 if (!dynamicMaskData.NeedsValidation && modificationType==Message.ModificationType.Add) 00355 parentLog.InsertRow(); 00356 00357 // Insert multilookup values for the validation table 00358 if (dynamicMaskData.NeedsValidation) 00359 { 00360 InsertMultilookupValues(dynamicMaskData.ParentDynamicMaskData, parentLog); 00361 } 00362 else 00363 { 00364 if (dynamicMaskData.ParentDynamicMaskData.MultilookupInsertionNeeded) 00365 InsertMultilookupValues(dynamicMaskData.ParentDynamicMaskData, parentLog); 00366 00367 if (dynamicMaskData.ParentDynamicMaskData.MultilookupDeletionNeeded) 00368 DeleteMultilookupValues(dynamicMaskData.ParentDynamicMaskData, parentLog); 00369 } 00370 parentLog.Close(); 00371 } 00372 00373 CallDataAdapterUpdate(dynamicMaskData, modificationType, mainMaskModificationType); 00374 00375 if (!dynamicMaskData.NeedsValidation && modificationType == Message.ModificationType.Add) 00376 log.InsertRow(); 00377 00378 // Insert multilookup values for the validation table 00379 if (dynamicMaskData.NeedsValidation) 00380 { 00381 InsertMultilookupValues(dynamicMaskData, log); 00382 } 00383 else 00384 { 00385 if (dynamicMaskData.MultilookupInsertionNeeded) 00386 InsertMultilookupValues(dynamicMaskData, log); 00387 00388 if (dynamicMaskData.MultilookupDeletionNeeded) 00389 DeleteMultilookupValues(dynamicMaskData, log); 00390 } 00391 log.Close(); 00392 00393 ts.Complete(); 00394 } 00395 00396 if (dynamicMaskData.NeedsValidation) 00397 returnMessage = RAIS.Common.Messages.CHANGES_SENT_FOR_VALIDATION; 00398 else 00399 returnMessage = RAIS.Common.Messages.CHANGES_SAVED; 00400 return true; 00401 } 00402 catch (Exception excep) 00403 { 00404 returnMessage = excep.Message + Constants.DATA_BASE_ERROR_MASSAGE; 00405 return false; 00406 } 00407 } 00413 public static bool DeleteRecord(DynamicMaskData dynamicMaskData, ref string returnMessage) 00414 { 00415 if (dynamicMaskData.DataSet.Tables[0].Rows.Count != 0) 00416 { 00417 try 00418 { 00419 using (TransactionScope ts = new TransactionScope()) 00420 { 00421 LoggingSystem log = new LoggingSystem(dynamicMaskData); 00422 log.Open(); 00423 log.DeleteRow(); 00424 dynamicMaskData.DataSet.Tables[0].Rows[0].Delete(); 00425 dynamicMaskData.DataAdapter.Update(dynamicMaskData.DataSet); 00426 log.Close(); 00427 ts.Complete(); 00428 } 00429 returnMessage = RAIS.Common.Messages.RECORD_DELETED; 00430 return true; 00431 } 00432 catch (Exception excep) 00433 { 00434 returnMessage = excep.Message + Constants.DATA_BASE_ERROR_MASSAGE; 00435 return false; 00436 } 00437 } 00438 else 00439 { 00440 returnMessage = ""; 00441 return true; 00442 } 00443 } 00450 public static bool DeleteRecordFromList(DynamicMaskData dynamicMaskData, string keyValue, ref string returnMessage) 00451 { 00452 try 00453 { 00454 string sqlString = string.Format("DELETE FROM [{0}] WHERE [{1}] = {2}" 00455 , dynamicMaskData.DynamicMask.Table.Name 00456 , dynamicMaskData.DynamicMask.Table.PrimaryKeyFieldName 00457 , keyValue); 00458 00459 using (SqlConnection sqlConnection = new SqlConnection(DataAccessUtilities.ConnectionString)) 00460 { 00461 sqlConnection.Open(); 00462 00463 using (SqlCommand sqlCommand = new SqlCommand(sqlString, sqlConnection)) 00464 { 00465 sqlCommand.CommandType = System.Data.CommandType.Text; 00466 sqlCommand.ExecuteNonQuery(); 00467 } 00468 } 00469 00470 returnMessage = RAIS.Common.Messages.RECORD_DELETED; 00471 return true; 00472 } 00473 catch (Exception excep) 00474 { 00475 returnMessage = excep.Message + Constants.DATA_BASE_ERROR_MASSAGE; 00476 return false; 00477 } 00478 } 00484 public static void GetChildFiltersLookupValues(DynamicMaskData dynamicMaskData) 00485 { 00486 foreach (KeyValuePair<int, DynamicMaskFilterData> kvp in dynamicMaskData.FiltersData) 00487 { 00488 DynamicMaskFilterData filterData = kvp.Value; 00489 00490 if ((filterData.ChildFilters != null) 00491 && (filterData.CurrentLookupValue != 0)) 00492 { 00493 foreach (KeyValuePair<int, DynamicMaskFilterData> kvpChild in filterData.ChildFilters) 00494 { 00495 DynamicMaskFilterData childFilterData = kvpChild.Value; 00496 00497 childFilterData.LookupValues = GetFilterLookupValues(childFilterData, dynamicMaskData.CurrentUser.ID); 00498 } 00499 } 00500 } 00501 } 00507 public static void GetDataSetForListMask(DynamicMaskData dynamicMaskData) 00508 { 00509 if (DataRoleRestrictionManagement.IsDataRoleRestricted(dynamicMaskData)) 00510 { 00511 DataRoleRestrictionManagement.ApplyDataRoleRestrictions(dynamicMaskData); 00512 } 00513 else 00514 { 00515 string sqlString = GetSelectString(dynamicMaskData); 00516 dynamicMaskData.DataAdapter = GetDataAdapter(sqlString, "", dynamicMaskData.DataSet); 00517 } 00518 } 00527 public static List<FieldData> FillFieldDataListFromTableRow(Table table, DataRow row, bool fromValidationTable, int userID) 00528 { 00529 List<FieldData> fieldsValues = new List<FieldData>(); 00530 Dictionary<int, FieldData> fieldDataValues = new Dictionary<int, FieldData>(); 00531 00532 foreach (KeyValuePair<string, Field> fieldPair in table.Fields) 00533 { 00534 Field currentField = fieldPair.Value; 00535 00536 string fieldName = currentField.Name; 00537 string fieldValue = ""; 00538 int currentLookupValue = 0; 00539 string currentLookupText = ""; 00540 Dictionary<int, string> lookupValues = null; 00541 Dictionary<int, string> selectedMultiLookupValues = null; 00542 00543 if (row != null 00544 && currentField.Type != Field.FieldType.SingleLookup 00545 && currentField.Type != Field.FieldType.MultipleLookup 00546 && currentField.Type != Field.FieldType.Label) 00547 { 00548 fieldValue = row[fieldName].ToString(); 00549 } 00550 00551 if (row != null 00552 && (currentField.Type == Field.FieldType.SingleLookup 00553 || currentField.Type == Field.FieldType.LookupPlusIntegerValue 00554 || currentField.Type == Field.FieldType.LookupPlusScientificValue 00555 || currentField.Type == Field.FieldType.MultipleLookup)) 00556 { 00557 if (currentField.RelatedTable.Fields == null) 00558 currentField.RelatedTable.Fields = TableManagement.GetFieldsWithRelatedByTable(currentField.RelatedTable); 00559 00560 fieldName = currentField.RelatedTable.VisibleTextFieldName; 00561 //bool resetMultilookup = false; 00562 MultilookupResetType resetMultilookup = MultilookupResetType.NotReset; 00563 string queryName = ReplaceParamsInQueryName(currentField.QueryName, fieldsValues,0, null, userID, 0, ref resetMultilookup); 00564 00565 if (currentField.Type == Field.FieldType.MultipleLookup) 00566 { 00567 lookupValues = GetNotSelectedMultipleLookupValues(table, queryName, row, currentField, fromValidationTable); 00568 selectedMultiLookupValues = GetSelectedMultipleLookupValues(table, row, currentField, fromValidationTable); 00569 } 00570 else 00571 { 00572 currentLookupValue = 00573 (row[currentField.ForeignKeyFieldName] is System.DBNull) ? 0 : 00574 (int)row[currentField.ForeignKeyFieldName]; 00575 if (currentLookupValue != 0) 00576 { 00577 Dictionary<int, string> nameFieldValues = GetNameFieldValuesByKey(currentField.RelatedTable, queryName, currentLookupValue); 00578 currentLookupText = ""; 00579 if (nameFieldValues.ContainsKey(currentLookupValue)) 00580 currentLookupText = nameFieldValues[currentLookupValue]; 00581 } 00582 } 00583 } 00584 00585 FieldData parentFieldData = null; 00586 if (currentField.ParentFieldID != null) 00587 { 00588 int fieldID = (int)currentField.ParentFieldID; 00589 if (fieldDataValues.ContainsKey(fieldID)) 00590 { 00591 parentFieldData = fieldDataValues[fieldID]; 00592 } 00593 } 00594 00595 FieldData fieldData = new FieldData(currentField, fieldValue, currentLookupValue, currentLookupText, lookupValues, selectedMultiLookupValues, parentFieldData); 00596 00597 00598 fieldDataValues.Add(currentField.ID, fieldData); 00599 fieldsValues.Add(fieldData); 00600 } 00601 FillIsUsedAsParameter(fieldsValues); 00602 return fieldsValues; 00603 } 00604 00605 private static void FillIsUsedAsParameter(List<FieldData> fieldsData) 00606 { 00607 foreach (FieldData fieldData in fieldsData) 00608 { 00609 string[] param = ParseQueryParameters(fieldData.Field.QueryName); 00610 if (param == null) 00611 continue; 00612 00613 foreach (string par in param) 00614 foreach (FieldData findFieldData in fieldsData) 00615 { 00616 if (findFieldData.Field.Name == par.Trim()) 00617 { 00618 findFieldData.IsUsedAsParameter = true; 00619 break; 00620 } 00621 } 00622 } 00623 } 00624 00625 private static string[] ParseQueryParameters(string queryName) 00626 { 00627 string[] paramArray = null; 00628 if (queryName == "" || !queryName.Contains("(")) 00629 return null; 00630 00631 int firstPost = queryName.IndexOf('(') + 1; 00632 int lastPost = queryName.IndexOf(')') - 1; 00633 string queryParams = queryName.Substring(firstPost, lastPost - firstPost + 1); 00634 00635 if (queryParams != "") 00636 paramArray = queryParams.Split(','); 00637 00638 return paramArray; 00639 } 00640 00650 public static SqlDataAdapter GetDataAdapter(string sqlString, string sqlForInsert, DataSet dataSet) 00651 { 00652 SqlDataAdapter tmpDataAdapter = new SqlDataAdapter(sqlString, DataAccessUtilities.ConnectionString); 00653 SqlCommandBuilder builder = new SqlCommandBuilder(tmpDataAdapter); 00654 00655 SqlDataAdapter dataAdapter = new SqlDataAdapter(sqlString, DataAccessUtilities.ConnectionString); 00656 if (sqlForInsert != string.Empty) 00657 { 00658 try 00659 { 00660 dataAdapter.DeleteCommand = builder.GetDeleteCommand(); 00661 dataAdapter.InsertCommand = builder.GetInsertCommand(); 00662 dataAdapter.InsertCommand.CommandText += " " + sqlForInsert; 00663 dataAdapter.InsertCommand.UpdatedRowSource = UpdateRowSource.FirstReturnedRecord; 00664 dataAdapter.UpdateCommand = builder.GetUpdateCommand(); 00665 } 00666 catch { } 00667 } 00668 00669 // Fill DataSet 00670 if (dataSet != null && dataSet.Tables.Count != 0) 00671 dataSet.Tables[0].Rows.Clear(); 00672 dataAdapter.Fill(dataSet); 00673 00674 return dataAdapter; 00675 } 00684 public static Protector GetProtector(string tableName, int rowID) 00685 { 00686 Protector protector = null; 00687 using (SqlConnection sqlConnection = new SqlConnection(DataAccessUtilities.ConnectionString)) 00688 { 00689 sqlConnection.Open(); 00690 00691 using (SqlCommand sqlCommand = new SqlCommand(Constants.SP_NAME_GetProtector, sqlConnection)) 00692 { 00693 sqlCommand.CommandType = System.Data.CommandType.StoredProcedure; 00694 DataAccessUtilities.AddParameter(sqlCommand, Constants.SP_PARAMETER_TableName, tableName); 00695 DataAccessUtilities.AddParameter(sqlCommand, Constants.SP_PARAMETER_RowID, rowID); 00696 00697 using (SqlDataReader sqlReader = sqlCommand.ExecuteReader()) 00698 { 00699 sqlReader.Read(); 00700 00701 if (sqlReader.HasRows) 00702 { 00703 protector = new Protector(sqlReader); 00704 } 00705 } 00706 } 00707 } 00708 00709 return protector; 00710 } 00718 public static Dictionary<int, DynamicMask> GetDynamicMasks(int maskID) 00719 { 00720 Dictionary<int, DynamicMask> dynamicMasks = new Dictionary<int, DynamicMask>(); 00721 00722 using (SqlConnection sqlConnection = new SqlConnection(DataAccessUtilities.ConnectionString)) 00723 { 00724 sqlConnection.Open(); 00725 00726 using (SqlCommand sqlCommand = new SqlCommand(Constants.SP_NAME_GetDynamicMasksByMaskID, sqlConnection)) 00727 { 00728 sqlCommand.CommandType = System.Data.CommandType.StoredProcedure; 00729 DataAccessUtilities.AddParameter(sqlCommand, Constants.SP_PARAMETER_MaskID, maskID); 00730 00731 using (SqlDataReader sqlReader = sqlCommand.ExecuteReader()) 00732 { 00733 while (sqlReader.Read()) 00734 { 00735 DynamicMask dynamicMask = new DynamicMask(sqlReader); 00736 if (dynamicMask.Table != null) 00737 dynamicMask.Table = TableManagement.GetTableWithRelatedFieldsByTableID(dynamicMask.Table.ID); 00738 dynamicMask.ConsistencyChecks = GetConsistencyChecks(dynamicMask.ID); 00739 00740 dynamicMasks.Add(dynamicMask.Order, dynamicMask); 00741 } 00742 } 00743 } 00744 } 00745 00746 Table authorTable = null; 00747 foreach (var dynamicMask in dynamicMasks.Values) 00748 { 00749 if (dynamicMask.Table == null) 00750 continue; 00751 00752 if (dynamicMask.AuthorizationType != "") 00753 { 00754 authorTable = dynamicMask.Table; 00755 break; 00756 } 00757 } 00758 if (authorTable != null) 00759 dynamicMasks[0].Table = authorTable; 00760 00761 return dynamicMasks; 00762 } 00771 public static bool AddDynamicMask(int subcategoryID, string maskName, DynamicMask dynamicMask, ref string returnMessage) 00772 { 00773 try 00774 { 00775 using (SqlConnection sqlConnection = new SqlConnection(DataAccessUtilities.ConnectionString)) 00776 { 00777 sqlConnection.Open(); 00778 00779 using (SqlCommand sqlCommand = new SqlCommand(Constants.SP_NAME_AddMaskAndDynamicMask, sqlConnection)) 00780 { 00781 sqlCommand.CommandType = System.Data.CommandType.StoredProcedure; 00782 DataAccessUtilities.AddParameter(sqlCommand, Constants.SP_PARAMETER_SubcategoryID, subcategoryID); 00783 DataAccessUtilities.AddParameter(sqlCommand, Constants.SP_PARAMETER_MaskName, maskName); 00784 DataAccessUtilities.AddParameter(sqlCommand, Constants.SP_PARAMETER_DynamicMaskName, dynamicMask.Name); 00785 DataAccessUtilities.AddParameter(sqlCommand, Constants.SP_PARAMETER_DynamicMaskTypeID, dynamicMask.TypeData.ID); 00786 DataAccessUtilities.AddParameter(sqlCommand, Constants.SP_PARAMETER_QueryName, dynamicMask.QueryName); 00787 DataAccessUtilities.AddParameter(sqlCommand, Constants.SP_PARAMETER_Grouping, dynamicMask.Grouping); 00788 DataAccessUtilities.AddParameter(sqlCommand, Constants.SP_PARAMETER_Widths, dynamicMask.Widths); 00789 DataAccessUtilities.AddParameter(sqlCommand, Constants.SP_PARAMETER_DynamicMaskFiltersXML, dynamicMask.FiltersXml); 00790 00791 sqlCommand.ExecuteNonQuery(); 00792 } 00793 } 00794 returnMessage = RAIS.Common.Messages.REPORT_MASK_ADDED; 00795 return true; 00796 } 00797 catch (Exception excep) 00798 { 00799 returnMessage = excep.Message + Constants.DATA_BASE_ERROR_MASSAGE; 00800 return false; 00801 } 00802 } 00809 public static bool SaveDynamicMask(DynamicMask dynamicMask, ref string returnMessage) 00810 { 00811 try 00812 { 00813 using (SqlConnection sqlConnection = new SqlConnection(DataAccessUtilities.ConnectionString)) 00814 { 00815 sqlConnection.Open(); 00816 00817 using (SqlCommand sqlCommand = new SqlCommand(Constants.SP_NAME_UpdateDynamicMask, sqlConnection)) 00818 { 00819 sqlCommand.CommandType = System.Data.CommandType.StoredProcedure; 00820 DataAccessUtilities.AddParameter(sqlCommand, Constants.SP_PARAMETER_DynamicMaskID, dynamicMask.ID); 00821 DataAccessUtilities.AddParameter(sqlCommand, Constants.SP_PARAMETER_QueryName, dynamicMask.QueryName); 00822 DataAccessUtilities.AddParameter(sqlCommand, Constants.SP_PARAMETER_Grouping, dynamicMask.Grouping); 00823 DataAccessUtilities.AddParameter(sqlCommand, Constants.SP_PARAMETER_Widths, dynamicMask.Widths); 00824 DataAccessUtilities.AddParameter(sqlCommand, Constants.SP_PARAMETER_Footer, dynamicMask.Footer); 00825 DataAccessUtilities.AddParameter(sqlCommand, Constants.SP_PARAMETER_DynamicMaskFiltersXML, dynamicMask.FiltersXml); 00826 00827 sqlCommand.ExecuteNonQuery(); 00828 } 00829 } 00830 returnMessage = RAIS.Common.Messages.DYNAMIC_MASK_SAVED; 00831 return true; 00832 } 00833 catch (Exception excep) 00834 { 00835 returnMessage = excep.Message + Constants.DATA_BASE_ERROR_MASSAGE; 00836 return false; 00837 } 00838 } 00845 public static bool RemoveDynamicMask(DynamicMask dynamicMask, ref string returnMessage) 00846 { 00847 try 00848 { 00849 using (SqlConnection sqlConnection = new SqlConnection(DataAccessUtilities.ConnectionString)) 00850 { 00851 sqlConnection.Open(); 00852 00853 using (SqlCommand sqlCommand = new SqlCommand(Constants.SP_NAME_DeleteDynamicMask, sqlConnection)) 00854 { 00855 sqlCommand.CommandType = System.Data.CommandType.StoredProcedure; 00856 DataAccessUtilities.AddParameter(sqlCommand, Constants.SP_PARAMETER_DynamicMaskID, dynamicMask.ID); 00857 00858 sqlCommand.ExecuteNonQuery(); 00859 } 00860 } 00861 returnMessage = RAIS.Common.Messages.REPORT_MASK_REMOVED; 00862 return true; 00863 } 00864 catch (Exception excep) 00865 { 00866 returnMessage = excep.Message + Constants.DATA_BASE_ERROR_MASSAGE; 00867 return false; 00868 } 00869 } 00877 public static List<ConsistencyCheck> GetConsistencyChecks(int dynamicMaskID) 00878 { 00879 List<ConsistencyCheck> consistencyChecks = new List<ConsistencyCheck>(); 00880 00881 using (SqlConnection sqlConnection = new SqlConnection(DataAccessUtilities.ConnectionString)) 00882 { 00883 sqlConnection.Open(); 00884 00885 using (SqlCommand sqlCommand = new SqlCommand(Constants.SP_NAME_GetConsistencyChecksByDynamicMaskID, sqlConnection)) 00886 { 00887 sqlCommand.CommandType = System.Data.CommandType.StoredProcedure; 00888 DataAccessUtilities.AddParameter(sqlCommand, Constants.SP_PARAMETER_DynamicMaskID, dynamicMaskID); 00889 00890 using (SqlDataReader sqlReader = sqlCommand.ExecuteReader()) 00891 { 00892 while (sqlReader.Read()) 00893 { 00894 ConsistencyCheck consistencyCheck = new ConsistencyCheck(sqlReader); 00895 consistencyChecks.Add(consistencyCheck); 00896 } 00897 } 00898 } 00899 } 00900 return consistencyChecks; 00901 } 00909 public static Dictionary<int, string> GetAllNameFieldValues(Table table, string queryName) 00910 { 00911 return GetNameFieldValuesWithFilter(table, queryName, "", 0); 00912 } 00918 public static RecordTypes GetRecordTypes() 00919 { 00920 RecordTypes recordTypes = new RecordTypes(); 00921 using (SqlConnection sqlConnection = new SqlConnection(DataAccessUtilities.ConnectionString)) 00922 { 00923 sqlConnection.Open(); 00924 00925 using (SqlCommand sqlCommand = new SqlCommand(Constants.SP_NAME_GetRecordTypes, sqlConnection)) 00926 { 00927 sqlCommand.CommandType = System.Data.CommandType.StoredProcedure; 00928 00929 using (SqlDataReader sqlReader = sqlCommand.ExecuteReader()) 00930 { 00931 while (sqlReader.Read()) 00932 { 00933 ConsistencyCheck.RecordTypeData recordTypeData = new ConsistencyCheck.RecordTypeData(sqlReader); 00934 00935 ConsistencyCheck.RecordType recordType; 00936 try 00937 { 00938 // Next operator throw an exeption if we do not have 00939 // element in enum which corresponds to record type in Record Type table 00940 // Catch it and throw exception with more clear message. 00941 recordType = recordTypeData.Type; 00942 } 00943 catch 00944 { 00945 throw new Exception("Record type " + recordTypeData.VisibleName + " exists in DB but does not exist in Enum ConsistencyCheck.RecordType. The running ASP server is not up to date with respect to the DB."); 00946 } 00947 00948 recordTypes.Add(recordTypeData.VisibleName, recordTypeData); 00949 } 00950 00951 // We checked that all enum elements exist in [Field Type] table, 00952 // so throw an exeption if number of elements in enum does not 00953 // equal number of elements in [Field Type] table 00954 if (Enum.GetNames(typeof(ConsistencyCheck.RecordType)).Length != recordTypes.Count) 00955 throw new Exception("There are record types which exist in Enum ConsistencyCheck.RecordType but does not exist in DB. The running DB is not up to date with respect to the ASP server."); 00956 } 00957 } 00958 } 00959 00960 return recordTypes; 00961 } 00969 public static bool AddConsistencyCheck(DynamicMask dynamicMask, ConsistencyCheck consistencyCheck, ref string returnMessage) 00970 { 00971 try 00972 { 00973 using (SqlConnection sqlConnection = new SqlConnection(DataAccessUtilities.ConnectionString)) 00974 { 00975 sqlConnection.Open(); 00976 00977 using (SqlCommand sqlCommand = new SqlCommand(Constants.SP_NAME_AddConsistencyCheck, sqlConnection)) 00978 { 00979 sqlCommand.CommandType = System.Data.CommandType.StoredProcedure; 00980 DataAccessUtilities.AddParameter(sqlCommand, Constants.SP_PARAMETER_ConsistencyCheckID, consistencyCheck.ID, System.Data.ParameterDirection.Output); 00981 DataAccessUtilities.AddParameter(sqlCommand, Constants.SP_PARAMETER_DynamicMaskID, dynamicMask.ID); 00982 DataAccessUtilities.AddParameter(sqlCommand, Constants.SP_PARAMETER_QueryName, consistencyCheck.QueryName); 00983 DataAccessUtilities.AddParameter(sqlCommand, Constants.SP_PARAMETER_Message, consistencyCheck.Message); 00984 DataAccessUtilities.AddParameter(sqlCommand, Constants.SP_PARAMETER_RecordTypeID, consistencyCheck.TypeData.ID); 00985 00986 sqlCommand.ExecuteNonQuery(); 00987 00988 consistencyCheck.ID = int.Parse(sqlCommand.Parameters[Constants.SP_PARAMETER_ConsistencyCheckID].Value.ToString()); 00989 } 00990 } 00991 returnMessage = RAIS.Common.Messages.CONSISTENCY_CHECK_ADDED; 00992 return true; 00993 } 00994 catch (Exception excep) 00995 { 00996 returnMessage = excep.Message + Constants.DATA_BASE_ERROR_MASSAGE; 00997 return false; 00998 } 00999 } 01006 public static bool SaveConsistencyCheck(ConsistencyCheck consistencyCheck, ref string returnMessage) 01007 { 01008 try 01009 { 01010 using (SqlConnection sqlConnection = new SqlConnection(DataAccessUtilities.ConnectionString)) 01011 { 01012 sqlConnection.Open(); 01013 01014 using (SqlCommand sqlCommand = new SqlCommand(Constants.SP_NAME_UpdateConsistencyCheck, sqlConnection)) 01015 { 01016 sqlCommand.CommandType = System.Data.CommandType.StoredProcedure; 01017 DataAccessUtilities.AddParameter(sqlCommand, Constants.SP_PARAMETER_ConsistencyCheckID, consistencyCheck.ID); 01018 DataAccessUtilities.AddParameter(sqlCommand, Constants.SP_PARAMETER_QueryName, consistencyCheck.QueryName); 01019 DataAccessUtilities.AddParameter(sqlCommand, Constants.SP_PARAMETER_Message, consistencyCheck.Message); 01020 DataAccessUtilities.AddParameter(sqlCommand, Constants.SP_PARAMETER_RecordTypeID, consistencyCheck.TypeData.ID); 01021 01022 sqlCommand.ExecuteNonQuery(); 01023 } 01024 } 01025 returnMessage = RAIS.Common.Messages.CONSISTENCY_CHECK_SAVED; 01026 return true; 01027 } 01028 catch (Exception excep) 01029 { 01030 returnMessage = excep.Message + Constants.DATA_BASE_ERROR_MASSAGE; 01031 return false; 01032 } 01033 } 01040 public static bool RemoveConsistencyCheck(ConsistencyCheck consistencyCheck, ref string returnMessage) 01041 { 01042 try 01043 { 01044 using (SqlConnection sqlConnection = new SqlConnection(DataAccessUtilities.ConnectionString)) 01045 { 01046 sqlConnection.Open(); 01047 01048 using (SqlCommand sqlCommand = new SqlCommand(Constants.SP_NAME_DeleteConsistencyCheck, sqlConnection)) 01049 { 01050 sqlCommand.CommandType = System.Data.CommandType.StoredProcedure; 01051 DataAccessUtilities.AddParameter(sqlCommand, Constants.SP_PARAMETER_ConsistencyCheckID, consistencyCheck.ID); 01052 01053 sqlCommand.ExecuteNonQuery(); 01054 } 01055 } 01056 returnMessage = RAIS.Common.Messages.CONSISTENCY_CHECK_REMOVED; 01057 return true; 01058 } 01059 catch (Exception excep) 01060 { 01061 returnMessage = excep.Message + Constants.DATA_BASE_ERROR_MASSAGE; 01062 return false; 01063 } 01064 } 01069 public static Dictionary<string, int> GetNonComplianceMajorCategories() 01070 { 01071 Dictionary<string, int> majorCategories = new Dictionary<string, int>(); 01072 01073 using (SqlConnection sqlConnection = new SqlConnection(DataAccessUtilities.ConnectionString)) 01074 { 01075 sqlConnection.Open(); 01076 01077 using (SqlCommand sqlCommand = new SqlCommand(Constants.SP_NAME_GetNonComplianceMajorCategories, sqlConnection)) 01078 { 01079 sqlCommand.CommandType = System.Data.CommandType.StoredProcedure; 01080 01081 using (SqlDataReader sqlReader = sqlCommand.ExecuteReader()) 01082 { 01083 while (sqlReader.Read()) 01084 { 01085 majorCategories.Add(sqlReader["Major Category Name"].ToString() 01086 , (int)sqlReader["PK Non-Compliance Major Category ID"]); 01087 } 01088 } 01089 } 01090 } 01091 return majorCategories; 01092 } 01098 public static Dictionary<string, int> GetNonComplianceCategoriesByMajorCategoryID(int majorCategoryID) 01099 { 01100 Dictionary<string, int> nonComplianceCategories = new Dictionary<string, int>(); 01101 01102 using (SqlConnection sqlConnection = new SqlConnection(DataAccessUtilities.ConnectionString)) 01103 { 01104 sqlConnection.Open(); 01105 01106 using (SqlCommand sqlCommand = new SqlCommand(Constants.SP_NAME_GetNonComplianceCategoriesByMajorCategoryID, sqlConnection)) 01107 { 01108 sqlCommand.CommandType = System.Data.CommandType.StoredProcedure; 01109 DataAccessUtilities.AddParameter(sqlCommand, Constants.SP_PARAMETER_NonComplianceMajorCategoryID, majorCategoryID); 01110 01111 using (SqlDataReader sqlReader = sqlCommand.ExecuteReader()) 01112 { 01113 while (sqlReader.Read()) 01114 { 01115 nonComplianceCategories.Add(sqlReader["Category Name"].ToString() 01116 , (int)sqlReader["PK Non-Compliance Category ID"]); 01117 } 01118 } 01119 } 01120 } 01121 return nonComplianceCategories; 01122 } 01123 #endregion 01124 //********************************************************************* 01125 #region Private Methods 01126 01131 static DynamicMaskTypes GetDynamicMaskTypes() 01132 { 01133 DynamicMaskTypes dynamicMaskTypes = new DynamicMaskTypes(); 01134 using (SqlConnection sqlConnection = new SqlConnection(DataAccessUtilities.ConnectionString)) 01135 { 01136 sqlConnection.Open(); 01137 01138 using (SqlCommand sqlCommand = new SqlCommand(Constants.SP_NAME_GetDynamicMaskTypes, sqlConnection)) 01139 { 01140 sqlCommand.CommandType = System.Data.CommandType.StoredProcedure; 01141 01142 using (SqlDataReader sqlReader = sqlCommand.ExecuteReader()) 01143 { 01144 while (sqlReader.Read()) 01145 { 01146 DynamicMask.DynamicMaskTypeData dynamicMaskTypeData = new DynamicMask.DynamicMaskTypeData(sqlReader); 01147 01148 DynamicMask.DynamicMaskType dynamicMaskType; 01149 try 01150 { 01151 // Next operator throw an exeption if we do not have 01152 // element in enum which corresponds to dynamic mask type in Dynamic Mask Type table 01153 // Catch it and throw exception with more clear message. 01154 dynamicMaskType = dynamicMaskTypeData.Type; 01155 } 01156 catch 01157 { 01158 throw new Exception("Dynamic mask type " + dynamicMaskTypeData.VisibleName + " exists in DB but does not exist in Enum DynamicMask.DynamicMaskType. The running ASP server is not up to date with respect to the DB."); 01159 } 01160 01161 dynamicMaskTypes.Add(dynamicMaskType, dynamicMaskTypeData); 01162 } 01163 01164 // We checked that all enum elements exist in [DynamicMask Type] table, 01165 // so throw an exeption if number of elements in enum does not 01166 // equal number of elements in [DynamicMask Type] table 01167 if (Enum.GetNames(typeof(DynamicMask.DynamicMaskType)).Length != dynamicMaskTypes.Count) 01168 throw new Exception("There are dynamic mask types which exist in Enum DynamicMask.DynamicMaskType but does not exist in DB. The running DB is not up to date with respect to the ASP server."); 01169 } 01170 } 01171 } 01172 01173 return dynamicMaskTypes; 01174 } 01183 static Dictionary<int, string> GetNameFieldValuesWithFilter(Table table, string queryName, string whereFieldName, int whereFieldValue) 01184 { 01185 return GetNameFieldValues(table, queryName, 0, whereFieldName, whereFieldValue); 01186 } 01195 static Dictionary<int, string> GetNameFieldValuesByKey(Table table, string queryName, int key) 01196 { 01197 return GetNameFieldValues(table, queryName, key, "", 0); 01198 } 01206 static Dictionary<int, DynamicMaskFilterData> GetDynamicMaskFiltersData(DynamicMaskData dynamicMaskData) 01207 { 01208 DynamicMask dynamicMask = dynamicMaskData.DynamicMask; 01209 01210 Dictionary<int, DynamicMaskFilterData> filters = new Dictionary<int, DynamicMaskFilterData>(); 01211 01212 using (SqlConnection sqlConnection = new SqlConnection(DataAccessUtilities.ConnectionString)) 01213 { 01214 sqlConnection.Open(); 01215 01216 using (SqlCommand sqlCommand = new SqlCommand(Constants.SP_NAME_GetDynamicMaskFiltersByDynamicMaskID, sqlConnection)) 01217 { 01218 sqlCommand.CommandType = System.Data.CommandType.StoredProcedure; 01219 DataAccessUtilities.AddParameter(sqlCommand, Constants.SP_PARAMETER_DynamicMaskID, dynamicMask.ID); 01220 01221 using (SqlDataReader sqlReader = sqlCommand.ExecuteReader()) 01222 { 01223 while (sqlReader.Read()) 01224 { 01225 DynamicMaskFilterData filterData = new DynamicMaskFilterData(); 01226 filterData.DynamicMaskFilter = new DynamicMaskFilter(sqlReader); 01227 //filterData.DynamicMaskFilter.Field = TableManagement.GetFieldByID(filterData.DynamicMaskFilter.Field.ID); 01228 filterData.DynamicMaskFilter.Table = TableManagement.GetTableByTableID(filterData.DynamicMaskFilter.Table.ID); 01229 01230 //filterData.ParentFilters = GetParentFilters(filterData, filters); 01231 01232 // Filter could have parent filters but must be initialized with 01233 // start lookup values (parent params is NULL in this case and Stored 01234 // procedure return all the values w/o filter) 01235 //if (filterData.ParentFilters.Count == 0 01236 // && filterData.DynamicMaskFilter.VisibleOnForm) 01237 01238 //if (filterData.DynamicMaskFilter.VisibleOnForm 01239 // && filterData.DynamicMaskFilter.Type == DynamicMaskFilter.FilterType.Lookup) 01240 //{ 01241 // filterData.LookupValues = GetFilterLookupValues(filterData, dynamicMaskData.CurrentUser.ID); 01242 //} 01243 filters.Add(filterData.DynamicMaskFilter.ID, filterData); 01244 } 01245 } 01246 } 01247 } 01248 01249 foreach (KeyValuePair<int, DynamicMaskFilterData> kvp in filters) 01250 { 01251 DynamicMaskFilterData filterData = kvp.Value; 01252 01253 filterData.ParentFilters = GetParentFilters(filterData, filters); 01254 01255 if (filterData.DynamicMaskFilter.VisibleOnForm 01256 && filterData.DynamicMaskFilter.Type == DynamicMaskFilter.FilterType.Lookup) 01257 { 01258 filterData.LookupValues = GetFilterLookupValues(filterData, dynamicMaskData.CurrentUser.ID); 01259 } 01260 } 01261 01262 return filters; 01263 } 01271 static Dictionary<int, DynamicMaskFilterData> GetParentFilters(DynamicMaskFilterData filterData 01272 , Dictionary<int, DynamicMaskFilterData> filters) 01273 { 01274 Dictionary<int, DynamicMaskFilterData> parentFilters = new Dictionary<int, DynamicMaskFilterData>(); 01275 01276 using (SqlConnection sqlConnection = new SqlConnection(DataAccessUtilities.ConnectionString)) 01277 { 01278 sqlConnection.Open(); 01279 01280 using (SqlCommand sqlCommand = new SqlCommand(Constants.SP_NAME_GetDynamicMaskFilterParametersByFilterID, sqlConnection)) 01281 { 01282 sqlCommand.CommandType = System.Data.CommandType.StoredProcedure; 01283 DataAccessUtilities.AddParameter(sqlCommand, Constants.SP_PARAMETER_DynamicMaskFilterID, filterData.DynamicMaskFilter.ID); 01284 01285 using (SqlDataReader sqlReader = sqlCommand.ExecuteReader()) 01286 { 01287 while (sqlReader.Read()) 01288 { 01289 int parentFilterID = (int)sqlReader["FK Parent Filter ID"]; 01290 int parentFilterOrder = (int)sqlReader["Order"]; 01291 01292 if (filters.ContainsKey(parentFilterID)) 01293 { 01294 DynamicMaskFilterData parentFilterData = filters[parentFilterID]; 01295 if (parentFilterData.ChildFilters == null) 01296 parentFilterData.ChildFilters = new Dictionary<int, DynamicMaskFilterData>(); 01297 parentFilterData.ChildFilters.Add(filterData.DynamicMaskFilter.ID, filterData); 01298 parentFilters.Add(parentFilterOrder, parentFilterData); 01299 } 01300 } 01301 } 01302 } 01303 } 01304 01305 return parentFilters; 01306 } 01307 01308 static Dictionary<int, string> GetSelectedMultipleLookupValues(Table table, DataRow row, Field field, bool fromValidationTable) 01309 { 01310 bool selected = true; 01311 return GetMultipleLookupValues(table, field.RelatedTable.Name, row, field, selected, fromValidationTable); 01312 } 01313 01314 static Dictionary<int, string> GetNotSelectedMultipleLookupValues(Table table, string queryName, DataRow row, Field field, bool fromValidationTable) 01315 { 01316 bool selected = false; 01317 return GetMultipleLookupValues(table, queryName, row, field, selected, fromValidationTable); 01318 } 01319 01320 static void InsertMultilookupValues(DynamicMaskData dynamicMaskData, LoggingSystem log) 01321 { 01322 Table table = dynamicMaskData.DynamicMask.Table; 01323 DataRow row = dynamicMaskData.DataSet.Tables[0].Rows[0]; 01324 DataRow validationRow = null; 01325 if (dynamicMaskData.NeedsValidation) 01326 validationRow = dynamicMaskData.ValidationDataSet.Tables[0].Rows[0]; 01327 01328 using (SqlConnection sqlConnection = new SqlConnection(DataAccessUtilities.ConnectionString)) 01329 { 01330 sqlConnection.Open(); 01331 01332 using (SqlCommand sqlCommand = new SqlCommand("", sqlConnection)) 01333 { 01334 sqlCommand.CommandType = System.Data.CommandType.Text; 01335 foreach (FieldData fieldData in dynamicMaskData.CurrentRowValues) 01336 { 01337 string sqlString = ""; 01338 01339 if (dynamicMaskData.NeedsValidation) 01340 { 01341 if (fieldData.NewMultiLookupValues != null 01342 && fieldData.NewMultiLookupValues.Count != 0) 01343 { 01344 foreach (KeyValuePair<int, string> kvp in fieldData.NewMultiLookupValues) 01345 { 01346 sqlString = string.Format( 01347 "INSERT [{0}]" 01348 + " (" 01349 + " [{0}].[{1}]" 01350 + " , [{0}].[{2}]" 01351 + " , [{0}].[{3}]" 01352 + " , [{0}].[FK Modification Type ID]" 01353 + " )" 01354 + " VALUES " 01355 + "({4}, {5}, {6}, {7})" 01356 , table.Name + " " + fieldData.Field.RelatedTable.Name + " Validation" 01357 , table.ValidationForeignKeyFieldName 01358 , table.ForeignKeyFieldName 01359 , fieldData.Field.ForeignKeyFieldName 01360 01361 , validationRow[table.ValidationPrimaryKeyFieldName] 01362 , (row[table.PrimaryKeyFieldName].ToString() == "") ? "null" : row[table.PrimaryKeyFieldName].ToString() 01363 , kvp.Key 01364 , MessageManagement.ModificationTypes[Message.ModificationType.Add].ID 01365 ); 01366 01367 sqlCommand.CommandText = sqlString; 01368 sqlCommand.ExecuteNonQuery(); 01369 } 01370 } 01371 } 01372 else 01373 { 01374 if (fieldData.MultiLookupToInsert != null 01375 && fieldData.MultiLookupToInsert.Count != 0) 01376 { 01377 foreach (KeyValuePair<int, string> kvp in fieldData.MultiLookupToInsert) 01378 { 01379 sqlString = string.Format( 01380 "INSERT [{0}]" 01381 + " ([{0}].[{1}], [{0}].[{2}])" 01382 + " VALUES " 01383 + "({3}, {4})" 01384 , table.Name + " " + fieldData.Field.RelatedTable.Name 01385 , table.MultipleLookupForeignKey 01386 , fieldData.Field.ForeignKeyFieldName 01387 , row[table.PrimaryKeyFieldName] 01388 , kvp.Key 01389 ); 01390 01391 sqlCommand.CommandText = sqlString; 01392 sqlCommand.ExecuteNonQuery(); 01393 log.WriteMultiLookupInsert(table.Name + " " + fieldData.Field.RelatedTable.Name, table.MultipleLookupForeignKey, 01394 fieldData.Field.ForeignKeyFieldName, row[table.PrimaryKeyFieldName].ToString(), kvp.Key.ToString()); 01395 } 01396 } 01397 } 01398 } 01399 } 01400 } 01401 } 01402 01403 static void DeleteMultilookupValues(DynamicMaskData dynamicMaskData, LoggingSystem log) 01404 { 01405 Table table = dynamicMaskData.DynamicMask.Table; 01406 DataRow row = dynamicMaskData.DataSet.Tables[0].Rows[0]; 01407 DataRow validationRow = null; 01408 if (dynamicMaskData.NeedsValidation) 01409 validationRow = dynamicMaskData.ValidationDataSet.Tables[0].Rows[0]; 01410 01411 using (SqlConnection sqlConnection = new SqlConnection(DataAccessUtilities.ConnectionString)) 01412 { 01413 sqlConnection.Open(); 01414 01415 using (SqlCommand sqlCommand = new SqlCommand("", sqlConnection)) 01416 { 01417 sqlCommand.CommandType = System.Data.CommandType.Text; 01418 foreach (FieldData fieldData in dynamicMaskData.CurrentRowValues) 01419 { 01420 if (fieldData.MultiLookupToDelete != null 01421 && fieldData.MultiLookupToDelete.Count != 0) 01422 { 01423 foreach (KeyValuePair<int, string> kvp in fieldData.MultiLookupToDelete) 01424 { 01425 string sqlString; 01426 01427 if (dynamicMaskData.NeedsValidation) 01428 { 01429 sqlString = string.Format( 01430 "INSERT [{0}]" 01431 + " (" 01432 + " [{0}].[{1}]" 01433 + " , [{0}].[{2}]" 01434 + " , [{0}].[{3}]" 01435 + " , [{0}].[FK Modification Type ID]" 01436 + " )" 01437 + " VALUES " 01438 + "({4}, {5}, {6}, {7})" 01439 , table.Name + " " + fieldData.Field.RelatedTable.Name + " Validation" 01440 , table.ValidationForeignKeyFieldName 01441 , table.ForeignKeyFieldName 01442 , fieldData.Field.ForeignKeyFieldName 01443 01444 , validationRow[table.ValidationPrimaryKeyFieldName] 01445 , (row[table.PrimaryKeyFieldName].ToString() == "") ? "null" : row[table.PrimaryKeyFieldName].ToString() 01446 , kvp.Key 01447 , MessageManagement.ModificationTypes[Message.ModificationType.Delete].ID 01448 ); 01449 } 01450 else 01451 { 01452 log.WriteMultiLookupDelete(table.Name + " " + fieldData.Field.RelatedTable.Name, table.MultipleLookupForeignKey, 01453 fieldData.Field.ForeignKeyFieldName, row[table.PrimaryKeyFieldName].ToString(), kvp.Key.ToString()); 01454 sqlString = string.Format( 01455 "DELETE FROM [{0}]" 01456 + " WHERE [{0}].[{1}] = {3} AND [{0}].[{2}] = {4}" 01457 , table.Name + " " + fieldData.Field.RelatedTable.Name 01458 , table.MultipleLookupForeignKey 01459 , fieldData.Field.ForeignKeyFieldName 01460 , row[table.PrimaryKeyFieldName] 01461 , kvp.Key 01462 ); 01463 } 01464 01465 sqlCommand.CommandText = sqlString; 01466 sqlCommand.ExecuteNonQuery(); 01467 } 01468 } 01469 } 01470 } 01471 } 01472 } 01473 01474 static void CallDataAdapterUpdate(DynamicMaskData dynamicMaskData, Message.ModificationType modificationType, Message.ModificationType mainMaskModificationType) 01475 { 01476 if (!dynamicMaskData.NeedsValidation) 01477 { 01478 dynamicMaskData.DataAdapter.Update(dynamicMaskData.DataSet); 01479 01480 if (dynamicMaskData.IsMultipaged && dynamicMaskData.ParentID == 0) 01481 dynamicMaskData.ParentID = dynamicMaskData.CurrentRowKeyValue; 01482 } 01483 else 01484 { 01485 // Save row in validation table 01486 dynamicMaskData.ValidationDataAdapter.Update(dynamicMaskData.ValidationDataSet); 01487 01488 // Save validation message 01489 if (!(dynamicMaskData.IsHistoryMask && (mainMaskModificationType == Message.ModificationType.Add))) 01490 { 01491 CreateValidationMessage(dynamicMaskData, modificationType); 01492 } 01493 else 01494 { 01495 SaveValidationMessageHistoryRowID(dynamicMaskData); 01496 } 01497 } 01498 } 01499 01500 static void CreateValidationMessage(DynamicMaskData dynamicMaskData 01501 , Message.ModificationType modificationType) 01502 { 01503 bool success; 01504 string errorMessage = string.Empty; 01505 01506 Message message = new Message(); 01507 message.UserFromID = dynamicMaskData.CurrentUser.ID; 01508 message.UserToID = null; 01509 message.TableID = dynamicMaskData.DynamicMask.Table.ID; 01510 message.RowID = (int)dynamicMaskData.ValidationDataSet.Tables[0].Rows[0][dynamicMaskData.DynamicMask.Table.ValidationPrimaryKeyFieldName]; ; 01511 message.Modification = modificationType; 01512 message.Validation = Message.ValidationStatus.NotValidated; 01513 01514 success = MessageManagement.AddMessage(message, ref errorMessage); 01515 if (success) 01516 dynamicMaskData.ValidationMessageID = message.ID; 01517 else 01518 throw new Exception(errorMessage); 01519 } 01520 01521 static void SaveValidationMessageHistoryRowID(DynamicMaskData dynamicMaskData) 01522 { 01523 bool success; 01524 string errorMessage = string.Empty; 01525 01526 int historyRowID = (int)dynamicMaskData.ValidationDataSet.Tables[0].Rows[0][dynamicMaskData.DynamicMask.Table.ValidationPrimaryKeyFieldName]; ; 01527 success = MessageManagement.SaveMessageHistoryRowID(dynamicMaskData.ValidationMessageID, historyRowID, ref errorMessage); 01528 if (!success) 01529 throw new Exception(errorMessage); 01530 } 01531 01532 static void CreateValidationMessages(DynamicMaskData dynamicMaskData 01533 , Message.ModificationType modificationType, Users validators) 01534 { 01535 bool success; 01536 string errorMessage = string.Empty; 01537 01538 Message message = new Message(); 01539 message.UserFromID = dynamicMaskData.CurrentUser.ID; 01540 message.TableID = dynamicMaskData.DynamicMask.Table.ID; 01541 message.RowID = (int)dynamicMaskData.ValidationDataSet.Tables[0].Rows[0][dynamicMaskData.DynamicMask.Table.ValidationPrimaryKeyFieldName]; ; 01542 message.Modification = modificationType; 01543 message.Validation = Message.ValidationStatus.NotValidated; 01544 01545 foreach (KeyValuePair<string, UserData> kvp in validators) 01546 { 01547 message.UserToID = kvp.Value.ID; 01548 01549 success = MessageManagement.AddMessage(message, ref errorMessage); 01550 if (!success) 01551 throw new Exception(errorMessage); 01552 } 01553 } 01554 01555 //static string ReplaceParamsInQueryName(string queryString, List<FieldData> currentRowValues, FieldData currentFieldData, int userID, int primaryKey, ref bool resetMultilookup) 01556 static string ReplaceParamsInQueryName(string queryString, List<FieldData> currentRowValues, int SelectedFacilityID, FieldData currentFieldData, int userID, int primaryKey, ref MultilookupResetType resetMultilookup) 01557 { 01558 //string queryName = currentField.QueryName; 01559 string queryName = queryString; 01560 string[] paramArray = ParseQueryParameters(queryString); 01561 Query query = null; 01562 01563 if (paramArray != null) 01564 { 01565 int firstPost = queryName.IndexOf('(') + 1; 01566 query = TableManagement.GetQueryByQueryName(queryString.Substring(0, firstPost - 1)); 01567 } 01568 else query = TableManagement.GetQueryByQueryName(queryString); 01569 01570 if (query.Type == Query.QueryType.RanAutogeneration) 01571 { 01572 string paramStr=""; 01573 bool firstParam=true; 01574 Dictionary<string, int> Preselected_RAN = new Dictionary<string, int>(); 01575 if (System.Web.HttpContext.Current.Session["Preselected_RAN"] != null) 01576 { 01577 Preselected_RAN = (Dictionary<string, int>)System.Web.HttpContext.Current.Session["Preselected_RAN"]; 01578 } 01579 queryName = "[" + query.Name + "]"; 01580 foreach (QueryParameter par in query.QueryParameters.Values) 01581 { 01582 if (!firstParam) 01583 paramStr = paramStr + ","; 01584 if (Preselected_RAN.ContainsKey(par.VisibleName.Trim())) 01585 paramStr = paramStr + Preselected_RAN[par.VisibleName.Trim()].ToString(); 01586 else 01587 paramStr = paramStr + "0"; 01588 firstParam = false; 01589 } 01590 queryName = queryName + " " + paramStr; 01591 01592 queryName = "EXEC " + queryName; 01593 return queryName; 01594 } 01595 01596 if (paramArray != null) 01597 { 01598 int firstPost = queryName.IndexOf('(') + 1; 01599 int lastPost = queryName.IndexOf(')') - 1; 01600 string queryParams = queryName.Substring(firstPost, lastPost - firstPost + 1); 01601 string newParams = ""; 01602 bool firstParam = true; 01603 01604 foreach (string queryParam in paramArray) 01605 { 01606 string trimmedQueryParam = queryParam.Trim(); 01607 string paramValue = "null"; 01608 01609 if (trimmedQueryParam == Common.Constants.PK_PARAMETER_NAME) 01610 { 01611 paramValue = primaryKey.ToString(); 01612 } 01613 else if (trimmedQueryParam == Common.Constants.USER_PARAMETER_NAME) 01614 { 01615 paramValue = userID.ToString(); 01616 } 01617 else if (trimmedQueryParam == Common.Constants.NON_COMPLIANCE_MAJOR_CATEGORY_PARAMETER_NAME 01618 && currentFieldData != null) 01619 { 01620 paramValue = (currentFieldData.NonComplianceMajorCategoryID == 0) 01621 ? "null" 01622 : currentFieldData.NonComplianceMajorCategoryID.ToString(); 01623 //resetMultilookup = true; 01624 resetMultilookup = MultilookupResetType.ResetSource; 01625 } 01626 else if (trimmedQueryParam == Common.Constants.NON_COMPLIANCE_CATEGORY_PARAMETER_NAME 01627 && currentFieldData != null) 01628 { 01629 paramValue = (currentFieldData.NonComplianceCategoryID == 0) 01630 ? "null" 01631 : currentFieldData.NonComplianceCategoryID.ToString(); 01632 //resetMultilookup = true; 01633 resetMultilookup = MultilookupResetType.ResetSource; 01634 } 01635 01636 else if (trimmedQueryParam == "SelectedFacilityID") 01637 { 01638 paramValue = SelectedFacilityID.ToString(); 01639 } 01640 01641 01642 else 01643 { 01644 FieldData findField = null; 01645 foreach (FieldData findFieldData in currentRowValues) 01646 { 01647 if (findFieldData.Field.Name == trimmedQueryParam) 01648 { 01649 findFieldData.IsUsedAsParameter = true; 01650 findField = findFieldData; 01651 break; 01652 } 01653 } 01654 01655 if (findField != null) 01656 { 01657 //if (!resetMultilookup) 01658 // resetMultilookup = findField.RaisedPostback; 01659 if (resetMultilookup != MultilookupResetType.ResetAll 01660 && findField.RaisedPostback) 01661 resetMultilookup = MultilookupResetType.ResetAll; 01662 01663 if (findField.Field.Type == Field.FieldType.SingleLookup) 01664 { 01665 if (findField.CurrentLookupValue != 0) 01666 paramValue = findField.CurrentLookupValue.ToString(); 01667 } 01668 else if (findField.Field.Type == Field.FieldType.MultipleLookup) 01669 { 01670 paramValue = findField.NewMultiLookupValuesInXml; 01671 } 01672 else 01673 { 01674 if (findField.Field.Type == Field.FieldType.Date) 01675 { 01676 //DateTimeFormatInfo myDTFI = new CultureInfo("en-US", false).DateTimeFormat; 01677 DateTimeFormatInfo myDTFI = new CultureInfo("en-US", true).DateTimeFormat; 01678 01679 DateTime dt; 01680 if (DateTime.TryParse(findField.Value, out dt)) 01681 paramValue = string.Format("'{0}'", dt.ToString(myDTFI)); 01682 else 01683 paramValue = string.Format("'{0}'", findField.Value); 01684 } 01685 else if (!(findField.Value == string.Empty)) 01686 { 01687 paramValue = string.Format("'{0}'", findField.Value); 01688 } 01689 } 01690 } 01691 } 01692 newParams += ((firstParam) ? "" : ", ") + paramValue; 01693 firstParam = false; 01694 } 01695 01696 queryName = queryName.Replace("(" + queryParams + ")", "(" + newParams + ")"); 01697 } 01698 01699 return queryName; 01700 } 01701 01702 static void FillRowsFromFieldsValues(DynamicMaskData dynamicMaskData, Message.ModificationType modificationType, LoggingSystem log) 01703 { 01704 bool isInsertionNeeded = false; 01705 bool isDeletionNeeded = false; 01706 01707 DataRow row; 01708 // Fill validation table row 01709 if (dynamicMaskData.NeedsValidation) 01710 { 01711 dynamicMaskData.AddValidationRow(); 01712 row = dynamicMaskData.ValidationCurrentRow; 01713 01714 row[Common.Constants.USER_FK_FIELD_NAME] = dynamicMaskData.CurrentUser.ID; 01715 row[dynamicMaskData.DynamicMask.Table.ForeignKeyFieldName] 01716 = dynamicMaskData.CurrentRow[dynamicMaskData.DynamicMask.Table.PrimaryKeyFieldName]; 01717 row[Common.Constants.VALIDATION_STATUS_FK_FIELD_NAME] = MessageManagement.ValidationStatuses[Message.ValidationStatus.NotValidated].ID; 01718 row[Common.Constants.TIME_STAMP_FIELD_NAME] = DateTime.Now; 01719 01720 FillTableRowFromFieldDataList(row, dynamicMaskData, modificationType, log, ref isInsertionNeeded, ref isDeletionNeeded); 01721 01722 dynamicMaskData.ValidationMultilookupInsertionNeeded = isInsertionNeeded; 01723 dynamicMaskData.ValidationMultilookupDeletionNeeded = isDeletionNeeded; 01724 } 01725 else 01726 { 01727 row = dynamicMaskData.CurrentRow; 01728 row[Common.Constants.TIME_STAMP_FIELD_NAME] = DateTime.Now; 01729 01730 FillTableRowFromFieldDataList(row, dynamicMaskData, modificationType, log, ref isInsertionNeeded, ref isDeletionNeeded); 01731 01732 dynamicMaskData.MultilookupInsertionNeeded = isInsertionNeeded; 01733 dynamicMaskData.MultilookupDeletionNeeded = isDeletionNeeded; 01734 } 01735 } 01736 01737 static void FillTableRowFromFieldDataList(DataRow row, DynamicMaskData dynamicMaskData, Message.ModificationType modificationType, LoggingSystem log, ref bool isInsertionNeeded, ref bool isDeletionNeeded) 01738 { 01739 isInsertionNeeded = false; 01740 isDeletionNeeded = false; 01741 01742 foreach (FieldData fieldData in dynamicMaskData.CurrentRowValues) 01743 { 01744 // User typed fields 01745 if (fieldData.Field.Type != Field.FieldType.SingleLookup 01746 && fieldData.Field.Type != Field.FieldType.MultipleLookup 01747 && fieldData.Field.Type != Field.FieldType.Label 01748 && (fieldData.Field.Type == Field.FieldType.Date 01749 || !((fieldData.Field.Type == Field.FieldType.Numeric 01750 || fieldData.Field.Type == Field.FieldType.LookupPlusIntegerValue 01751 || fieldData.Field.Type == Field.FieldType.LookupPlusScientificValue) 01752 && fieldData.Value == string.Empty)) 01753 ) 01754 { 01755 if (modificationType == Message.ModificationType.Edit && !dynamicMaskData.NeedsValidation) 01756 log.UpdateValue(row[fieldData.Field.Name].ToString(), fieldData.Value, fieldData); 01757 if ((fieldData.Field.Type == Field.FieldType.Date) && (fieldData.Value == string.Empty)) 01758 { 01759 row[fieldData.Field.Name] = DBNull.Value; 01760 } 01761 else 01762 { 01763 row[fieldData.Field.Name] = fieldData.Value; 01764 } 01765 } 01766 01767 // Lookup fields 01768 if (fieldData.Field.Type == Field.FieldType.SingleLookup 01769 || fieldData.Field.Type == Field.FieldType.LookupPlusIntegerValue 01770 || fieldData.Field.Type == Field.FieldType.LookupPlusScientificValue) 01771 { 01772 if (fieldData.CurrentLookupValue != 0) 01773 { 01774 if (modificationType == Message.ModificationType.Edit && !dynamicMaskData.NeedsValidation) 01775 log.UpdateValue(row[fieldData.Field.ForeignKeyFieldName].ToString(), fieldData.CurrentLookupValue.ToString(), fieldData); 01776 row[fieldData.Field.ForeignKeyFieldName] = fieldData.CurrentLookupValue; 01777 } 01778 else 01779 { 01780 if (modificationType == Message.ModificationType.Edit && row[fieldData.Field.ForeignKeyFieldName] != DBNull.Value && !dynamicMaskData.NeedsValidation) 01781 log.UpdateValue(row[fieldData.Field.ForeignKeyFieldName].ToString(), null, fieldData); 01782 row[fieldData.Field.ForeignKeyFieldName] = DBNull.Value; 01783 } 01784 } 01785 01786 // Multilookup fields 01787 if (fieldData.Field.Type == Field.FieldType.MultipleLookup) 01788 { 01789 if (fieldData.MultiLookupToInsert == null) 01790 fieldData.MultiLookupToInsert = new Dictionary<int, string>(); 01791 else 01792 fieldData.MultiLookupToInsert.Clear(); 01793 01794 foreach (KeyValuePair<int, string> kvp in fieldData.NewMultiLookupValues) 01795 { 01796 if (!fieldData.SelectedMultiLookupValues.ContainsKey(kvp.Key)) 01797 { 01798 fieldData.MultiLookupToInsert.Add(kvp.Key, kvp.Value); 01799 isInsertionNeeded = true; 01800 } 01801 } 01802 01803 if (fieldData.MultiLookupToDelete == null) 01804 fieldData.MultiLookupToDelete = new Dictionary<int, string>(); 01805 else 01806 fieldData.MultiLookupToDelete.Clear(); 01807 01808 foreach (KeyValuePair<int, string> kvp in fieldData.SelectedMultiLookupValues) 01809 { 01810 if (!fieldData.NewMultiLookupValues.ContainsKey(kvp.Key)) 01811 { 01812 fieldData.MultiLookupToDelete.Add(kvp.Key, kvp.Value); 01813 isDeletionNeeded = true; 01814 } 01815 } 01816 } 01817 } 01818 } 01819 #endregion 01820 //********************************************************************* 01821 #region SELECT Building Methods 01822 01827 public static bool ValidateConsistencyChecks(DynamicMaskData dynamicMaskData, ref string returnMessage) 01828 { 01829 try 01830 { 01831 bool succsess = true; 01832 01833 if (dynamicMaskData.DynamicMask.ConsistencyChecks != null) 01834 { 01835 int inconsistentCheckNumber = 1; 01836 foreach (ConsistencyCheck consistencyCheck in dynamicMaskData.DynamicMask.ConsistencyChecks) 01837 { 01838 if (consistencyCheck.Type == ConsistencyCheck.RecordType.AllRecords 01839 || consistencyCheck.Type == ConsistencyCheck.RecordType.NewRecords 01840 && dynamicMaskData.IsNewRow 01841 || consistencyCheck.Type == ConsistencyCheck.RecordType.OldRecords 01842 && !dynamicMaskData.IsNewRow 01843 ) 01844 { 01845 //bool resetMultilookup = false; 01846 MultilookupResetType resetMultilookup = MultilookupResetType.NotReset; 01847 string queryName = ReplaceParamsInQueryName( 01848 consistencyCheck.QueryName 01849 , dynamicMaskData.CurrentRowValues 01850 , dynamicMaskData.SelectedFacilityID 01851 , null 01852 , dynamicMaskData.CurrentUser.ID 01853 , dynamicMaskData.CurrentRowKeyValue 01854 , ref resetMultilookup); 01855 01856 queryName = "[" + queryName; 01857 if (queryName.Contains("(")) 01858 queryName = queryName.Replace("(", "]("); 01859 else 01860 queryName = queryName + "]"; 01861 01862 string sqlString = string.Format("SELECT * FROM {0}", queryName); 01863 01864 using (SqlConnection sqlConnection = new SqlConnection(DataAccessUtilities.ConnectionString)) 01865 { 01866 sqlConnection.Open(); 01867 01868 using (SqlCommand sqlCommand = new SqlCommand(sqlString, sqlConnection)) 01869 { 01870 sqlCommand.CommandType = System.Data.CommandType.Text; 01871 01872 using (SqlDataReader sqlReader = sqlCommand.ExecuteReader()) 01873 { 01874 if (sqlReader.HasRows) 01875 { 01876 succsess = false; 01877 returnMessage += 01878 ((returnMessage != "") ? "\n" : "") 01879 + inconsistentCheckNumber.ToString() + ") " 01880 + consistencyCheck.Message; 01881 inconsistentCheckNumber++; 01882 } 01883 } 01884 } 01885 } 01886 } 01887 } 01888 } 01889 01890 return succsess; 01891 } 01892 catch (Exception excep) 01893 { 01894 returnMessage = excep.Message + Constants.DATA_BASE_ERROR_MASSAGE; 01895 return false; 01896 } 01897 } 01906 public static string[] GetSelectStrings(Table table, int parentTableID, int parentID, int rowID, bool addRow) 01907 { 01908 string foreignKeyFieldName = table.GetForeignKeyFieldNameByTableID(parentTableID); 01909 01910 string[] sqlStrings = new string[Enum.GetValues(typeof(SelectType)).Length]; 01911 01912 // For Insert string 01913 sqlStrings[(int)SelectType.ForInsert] = string.Format( 01914 "SELECT * FROM [{0}] WHERE [{1}] = @@IDENTITY" 01915 , table.Name, table.PrimaryKeyFieldName); 01916 01917 01918 // Main Query 01919 //sqlStrings[(int)SelectType.MainSelect] = string.Format( 01920 // "SELECT * FROM [{0}] WHERE [{1}] = {2}" 01921 // , table.Name, table.PrimaryKeyFieldName, rowID); 01922 01923 //if (foreignKeyFieldName != "" && parentID != 0) 01924 //{ 01925 // sqlStrings[(int)SelectType.MainSelect] += string.Format( 01926 // " AND [{0}] = {1}" 01927 // , foreignKeyFieldName 01928 // , parentID); 01929 //} 01930 if (addRow) 01931 { 01932 sqlStrings[(int)SelectType.MainSelect] = string.Format( 01933 "SELECT * FROM [{0}] WHERE $IDENTITY = null" 01934 , table.Name); 01935 } 01936 else 01937 { 01938 List<string> conditions = new List<string>(); 01939 if (!(foreignKeyFieldName != "" && parentID != 0 && rowID == 0)) 01940 conditions.Add(string.Format("[{0}] = {1}", table.PrimaryKeyFieldName, rowID)); 01941 01942 if (foreignKeyFieldName != "" && parentID != 0) 01943 conditions.Add(string.Format("[{0}] = {1}", foreignKeyFieldName, parentID)); 01944 01945 sqlStrings[(int)SelectType.MainSelect] = string.Format( 01946 "SELECT * FROM [{0}] WHERE {1}" 01947 , table.Name, conditions[0]); 01948 01949 if (conditions.Count > 1) 01950 { 01951 sqlStrings[(int)SelectType.MainSelect] += string.Format( 01952 " AND {0}", conditions[1]); 01953 } 01954 } 01955 01956 // Main validation query 01957 sqlStrings[(int)SelectType.Validation] = string.Format( 01958 "SELECT * FROM [{0} Validation] WHERE $IDENTITY = null" 01959 , table.Name); 01960 01961 // Validation query for insert 01962 sqlStrings[(int)SelectType.ValidationForInsert] = string.Format( 01963 "SELECT * FROM [{0} Validation] WHERE [{1}] = @@IDENTITY" 01964 , table.Name, table.ValidationPrimaryKeyFieldName); 01965 01966 return sqlStrings; 01967 } 01976 static string GetSelectString(Table table, int parentTableID, int parentID, int rowID, bool forInsert) 01977 { 01978 string sqlString = ""; 01979 if (forInsert) 01980 { 01981 sqlString = string.Format( 01982 " SELECT * FROM [{0}] WHERE [{1}] = @@IDENTITY" 01983 , table.Name, table.PrimaryKeyFieldName); 01984 } 01985 else 01986 { 01987 string foreignKeyFieldName = table.GetForeignKeyFieldNameByTableID(parentTableID); 01988 01989 sqlString = string.Format( 01990 "SELECT * FROM [{0}] WHERE [{1}] = {2}" 01991 , table.Name, table.PrimaryKeyFieldName, rowID); 01992 01993 if (foreignKeyFieldName != "" && parentID != 0) 01994 { 01995 sqlString += string.Format( 01996 " AND [{0}] = {1}" 01997 , foreignKeyFieldName 01998 , parentID); 01999 } 02000 } 02001 02002 return sqlString; 02003 } 02011 static string GetSelectString(DynamicMaskData dynamicMaskData) 02012 { 02013 string sqlString = string.Format("SET LANGUAGE us_english\n" 02014 + "SELECT * FROM [{0}]" 02015 , (dynamicMaskData.DynamicMask.QueryName == "") 02016 ? dynamicMaskData.DynamicMask.Table.Name 02017 : dynamicMaskData.DynamicMask.QueryName); 02018 02019 if (dynamicMaskData.DynamicMask.QueryName != "") 02020 { 02021 SortedDictionary<int, DynamicMaskFilterData> queryOrderedFiltersData = new SortedDictionary<int, DynamicMaskFilterData>(); 02022 foreach (KeyValuePair<int, DynamicMaskFilterData> kvp in dynamicMaskData.FiltersData) 02023 { 02024 DynamicMaskFilterData filterData = kvp.Value; 02025 if (filterData.DynamicMaskFilter.UseInQuery) 02026 queryOrderedFiltersData.Add(filterData.DynamicMaskFilter.OrderInQuery, filterData); 02027 } 02028 02029 sqlString += "("; 02030 bool isFirstParameter = true; 02031 foreach (KeyValuePair<int, DynamicMaskFilterData> kvp in queryOrderedFiltersData) 02032 { 02033 DynamicMaskFilterData filterData = kvp.Value; 02034 if (filterData.DynamicMaskFilter.UseInQuery) 02035 { 02036 sqlString += (isFirstParameter) ? "" : ", "; 02037 if (filterData.DynamicMaskFilter.Name == Common.Constants.FACILITY_PARAMETER_NAME 02038 && !filterData.DynamicMaskFilter.VisibleOnForm) 02039 { 02040 sqlString += (dynamicMaskData.SelectedFacilityID == 0) 02041 ? "null" 02042 : dynamicMaskData.SelectedFacilityID.ToString(); 02043 } 02044 else 02045 if (filterData.DynamicMaskFilter.Name == Common.Constants.DEPARTMENT_PARAMETER_NAME 02046 && !filterData.DynamicMaskFilter.VisibleOnForm) 02047 { 02048 sqlString += (dynamicMaskData.SelectedDepartmentID == 0) 02049 ? "null" 02050 : dynamicMaskData.SelectedDepartmentID.ToString(); 02051 } 02052 else 02053 if (filterData.DynamicMaskFilter.Name == Common.Constants.USER_PARAMETER_NAME) 02054 { 02055 sqlString += (dynamicMaskData.CurrentUser.ID == 0) 02056 ? "null" 02057 : dynamicMaskData.CurrentUser.ID.ToString(); 02058 } 02059 else 02060 { 02061 if (filterData.DynamicMaskFilter.Type == DynamicMaskFilter.FilterType.DatesRage) 02062 { 02063 // Set end of the day 02064 DateTime dateUntil = filterData.DateUntil.AddSeconds(86399); 02065 02066 // Use SET LANGUAGE us-english command before SELECT 02067 // that's why use 'yyyy-mm-dd' date format for dates 02068 sqlString += string.Format("'{0}-{1}-{2} {3}', '{4}-{5}-{6} {7}'" 02069 , filterData.DateFrom.Year 02070 , filterData.DateFrom.Month 02071 , filterData.DateFrom.Day 02072 , filterData.DateFrom.TimeOfDay 02073 , dateUntil.Year 02074 , dateUntil.Month 02075 , dateUntil.Day 02076 , dateUntil.TimeOfDay); 02077 } 02078 else 02079 { 02080 sqlString += (filterData.CurrentLookupValue == 0) 02081 ? "null" 02082 : filterData.CurrentLookupValue.ToString(); 02083 } 02084 } 02085 02086 isFirstParameter = false; 02087 } 02088 } 02089 02090 sqlString += ")"; 02091 } 02092 else 02093 { 02094 if (dynamicMaskData.DynamicMask.AuthorizationType != string.Empty) 02095 { 02096 sqlString += string.Format(" WHERE [{0}] = '{1}'" 02097 , RAIS.Common.Constants.AUTHORIZATION_TYPE_FIELD_NAME 02098 , dynamicMaskData.DynamicMask.AuthorizationType); 02099 } 02100 } 02101 02102 if (dynamicMaskData.SortColumnNumber != 0 02103 && dynamicMaskData.DynamicMask.Type != DynamicMask.DynamicMaskType.ReportMask) 02104 { 02105 sqlString += string.Format(" ORDER BY {0} {1}" 02106 , dynamicMaskData.SortColumnNumber 02107 , dynamicMaskData.SortDirection); 02108 } 02109 02110 return sqlString; 02111 } 02121 static Dictionary<int, string> GetNameFieldValues(Table table, string queryName, int key, string whereFieldName, int whereFieldValue) 02122 { 02123 Dictionary<int, string> fieldValues = new Dictionary<int, string>(); 02124 bool selectFromTable = ((queryName == string.Empty) && (table.Name == table.QueryName)); 02125 02126 queryName = (queryName == string.Empty) ? table.QueryName : queryName; 02127 02128 queryName = "[" + queryName; 02129 if (queryName.Contains("(")) 02130 queryName = queryName.Replace("(", "]("); 02131 else 02132 queryName = queryName + "]"; 02133 02134 using (SqlConnection sqlConnection = new SqlConnection(DataAccessUtilities.ConnectionString)) 02135 { 02136 sqlConnection.Open(); 02137 02138 string sqlString = ""; 02139 if (selectFromTable) 02140 { 02141 string _fieldKey = ""; 02142 foreach (var field in table.Fields) 02143 { 02144 if (field.Value.Order == 1) 02145 { 02146 _fieldKey = field.Key; 02147 break; 02148 } 02149 } 02150 if (_fieldKey == "") 02151 { 02152 if (table.VisibleTextFieldName != "" 02153 && table.PrimaryKeyFieldName != table.VisibleTextFieldName) 02154 { 02155 sqlString = string.Format("SELECT [{0}], [{1}] FROM {2}" 02156 , table.PrimaryKeyFieldName 02157 , table.VisibleTextFieldName 02158 , queryName); 02159 } 02160 else 02161 { 02162 sqlString = string.Format("SELECT * FROM {1}" 02163 , table.PrimaryKeyFieldName 02164 , queryName); 02165 } 02166 } 02167 else 02168 { 02170 //sqlString = 02171 // string.Format("SELECT [{0}],[{2}] FROM {1}" 02172 // , table.PrimaryKeyFieldName 02173 // , queryName 02174 // , /*(table.Fields[_fieldKey].RelatedTable == null) */ 02175 // /*? */table.VisibleTextFieldName 02176 // /*: table.Fields[_fieldKey].RelatedTable.ForeignKeyFieldName*/); 02177 02178 //changed: 15.09.2011 / for tables with no visible fields 02179 if (table.VisibleTextFieldName == "") 02180 { 02181 sqlString = 02182 string.Format("SELECT [{0}] FROM {1}" 02183 , table.PrimaryKeyFieldName 02184 , queryName 02185 /*, /*(table.Fields[_fieldKey].RelatedTable == null) */ 02186 /*? table.VisibleTextFieldName */ 02187 /*: table.Fields[_fieldKey].RelatedTable.ForeignKeyFieldName*/); 02188 } 02189 else 02190 { 02191 sqlString = 02192 string.Format("SELECT [{0}],[{2}] FROM {1}" 02193 , table.PrimaryKeyFieldName 02194 , queryName 02195 , /*(table.Fields[_fieldKey].RelatedTable == null) */ 02196 /*? */table.VisibleTextFieldName 02197 /*: table.Fields[_fieldKey].RelatedTable.ForeignKeyFieldName*/); 02198 } 02199 } 02200 } 02201 02202 else 02203 sqlString = string.Format("SELECT * FROM {2}" 02204 , table.PrimaryKeyFieldName 02205 , table.VisibleTextFieldName 02206 , queryName); 02207 02208 if (key != 0) 02209 sqlString += string.Format(" WHERE [{0}] = {1}", table.PrimaryKeyFieldName, key); 02210 02211 if (whereFieldValue != 0) 02212 sqlString += string.Format(" WHERE [{0}] = {1}", whereFieldName, whereFieldValue); 02213 02214 if (table.VisibleTextFieldName != "" && selectFromTable) 02215 { 02216 sqlString += string.Format(" ORDER BY [{0}]", table.VisibleTextFieldName); 02217 if (table.Fields.ContainsKey(table.VisibleTextFieldName) 02218 && table.Fields[table.VisibleTextFieldName].Type == Field.FieldType.Date) 02219 { 02220 sqlString += " DESC"; 02221 } 02222 } 02223 02224 using (SqlCommand sqlCommand = new SqlCommand(sqlString, sqlConnection)) 02225 { 02226 sqlCommand.CommandType = System.Data.CommandType.Text; 02227 02228 using (SqlDataReader sqlReader = sqlCommand.ExecuteReader()) 02229 { 02230 while (sqlReader.Read()) 02231 { 02232 int keyValue; 02233 try 02234 { 02235 keyValue = (int)sqlReader[table.PrimaryKeyFieldName]; 02236 } 02237 catch (IndexOutOfRangeException e) 02238 { 02239 return null; 02240 } 02241 02242 if (!fieldValues.ContainsKey(keyValue)) 02243 { 02244 fieldValues.Add(keyValue 02245 , (sqlReader.FieldCount > 1) 02246 ? sqlReader[1].ToString() 02247 : sqlReader[table.PrimaryKeyFieldName].ToString()); 02248 } 02249 } 02250 } 02251 } 02252 } 02253 02254 return fieldValues; 02255 } 02265 static Dictionary<int, string> GetFilterLookupValues(DynamicMaskFilterData filterData, int userID) 02266 { 02267 Dictionary<int, string> fieldValues = new Dictionary<int, string>(); 02268 02269 using (SqlConnection sqlConnection = new SqlConnection(DataAccessUtilities.ConnectionString)) 02270 { 02271 sqlConnection.Open(); 02272 02273 string parameters = ""; 02274 bool firstParameter = true; 02275 foreach (KeyValuePair<int, DynamicMaskFilterData> kvp in filterData.ParentFilters) 02276 { 02277 DynamicMaskFilterData parentFilterData = kvp.Value; 02278 02279 if (parentFilterData.DynamicMaskFilter.Name == Common.Constants.USER_PARAMETER_NAME) 02280 parameters += ((firstParameter) ? "" : ",") + userID.ToString(); 02281 else 02282 parameters += ((firstParameter) ? "" : ",") 02283 + ((parentFilterData.CurrentLookupValue == 0) 02284 ? "NULL" 02285 : parentFilterData.CurrentLookupValue.ToString()); 02286 firstParameter = false; 02287 } 02288 02289 // Add UserID to parameters 02290 //parameters += ((firstParameter) ? "" : ",") + userID.ToString(); 02291 02292 string sqlString = string.Format( 02293 "SELECT * FROM [{0}]({1})" 02294 , filterData.DynamicMaskFilter.QueryName 02295 , parameters); 02296 02297 using (SqlCommand sqlCommand = new SqlCommand(sqlString, sqlConnection)) 02298 { 02299 sqlCommand.CommandType = System.Data.CommandType.Text; 02300 02301 using (SqlDataReader sqlReader = sqlCommand.ExecuteReader()) 02302 { 02303 while (sqlReader.Read()) 02304 { 02305 fieldValues.Add((int)sqlReader[0], sqlReader[1].ToString()); 02306 } 02307 } 02308 } 02309 } 02310 02311 return fieldValues; 02312 } 02313 02314 static Dictionary<int, string> GetMultipleLookupValues(Table table, string queryName, DataRow row, Field field, bool selected, bool fromValidationTable) 02315 { 02316 Dictionary<int, string> fieldValues = new Dictionary<int, string>(); 02317 queryName = (queryName == "") ? field.QueryName : queryName; 02318 02319 using (SqlConnection sqlConnection = new SqlConnection(DataAccessUtilities.ConnectionString)) 02320 { 02321 sqlConnection.Open(); 02322 02323 string sqlString = ""; 02324 if (queryName != "") 02325 { 02326 //string queryName = field.QueryName; 02327 queryName = "[" + queryName; 02328 if (queryName.Contains("(")) 02329 queryName = queryName.Replace("(", "]("); 02330 else 02331 queryName = queryName + "]"; 02332 02333 string pkValue = (!fromValidationTable) 02334 ? ((row[table.PrimaryKeyFieldName] is DBNull) ? 0.ToString() : row[table.PrimaryKeyFieldName].ToString()) 02335 : row[table.ValidationPrimaryKeyFieldName].ToString(); 02336 sqlString = string.Format( 02337 "SELECT {1}.*" 02338 + " FROM {2} {1}" 02339 + " LEFT JOIN [{3}] ON [{3}].[{4}] = {1}.[{0}]" 02340 + " AND [{3}].[{5}] = {6}" //((!selected && pkValue == 0.ToString()) ? "" : " AND [{3}].[{5}] = {6}") 02341 + " WHERE [{3}].[{4}] IS {7} NULL" 02342 , field.RelatedTable.PrimaryKeyFieldName 02343 , "TableAlias" 02344 , queryName 02345 , table.Name + " " + field.RelatedTable.Name + ((!fromValidationTable) ? "" : " Validation") 02346 , field.ForeignKeyFieldName 02347 , (!fromValidationTable) ? table.MultipleLookupForeignKey : table.ValidationForeignKeyFieldName 02348 , pkValue 02349 , (selected) ? "NOT" : "" 02350 ); 02351 } 02352 else 02353 { 02354 // Case when parameter queryName = "" and table.VisibleTextFieldName = "" 02355 sqlString = string.Format( 02356 "SELECT [{2}].[{0}], [{2}].[{1}]" 02357 + " FROM [{2}]" 02358 + " LEFT JOIN [{3}] ON [{3}].[{4}] = [{2}].[{0}]" 02359 + " AND [{3}].[{5}] = {6}" 02360 + " WHERE [{3}].[{4}] IS {7} NULL" 02361 + " ORDER BY [{2}].[{1}]" 02362 , field.RelatedTable.PrimaryKeyFieldName 02363 , field.RelatedTable.VisibleTextFieldName 02364 , field.RelatedTable.QueryName 02365 , table.Name + " " + field.RelatedTable.Name + ((!fromValidationTable) ? "" : " Validation") 02366 , field.ForeignKeyFieldName 02367 , (!fromValidationTable) ? table.MultipleLookupForeignKey : table.ValidationForeignKeyFieldName 02368 , (!fromValidationTable) 02369 ? ((row[table.PrimaryKeyFieldName] is DBNull) ? 0 : row[table.PrimaryKeyFieldName]) 02370 : row[table.ValidationPrimaryKeyFieldName] 02371 , (selected) ? "NOT" : "" 02372 ); 02373 } 02374 02375 using (SqlCommand sqlCommand = new SqlCommand(sqlString, sqlConnection)) 02376 { 02377 sqlCommand.CommandType = System.Data.CommandType.Text; 02378 02379 using (SqlDataReader sqlReader = sqlCommand.ExecuteReader()) 02380 { 02381 while (sqlReader.Read()) 02382 { 02383 int key = 0; 02384 string value = string.Empty; 02385 if (field.QueryName != string.Empty) 02386 { 02387 key = (int)sqlReader[0]; 02388 value = sqlReader[1].ToString(); 02389 } 02390 else 02391 { 02392 key = (int)sqlReader[field.RelatedTable.PrimaryKeyFieldName]; 02393 value = sqlReader[field.RelatedTable.VisibleTextFieldName].ToString(); 02394 } 02395 // Skip row if we have row with dulicate key 02396 // (it is possible if we have query which returns duplicates values) 02397 if (!fieldValues.ContainsKey(key)) 02398 fieldValues.Add(key, value); 02399 } 02400 } 02401 } 02402 } 02403 02404 return fieldValues; 02405 } 02411 static string GetNotLookupFieldValueFromQuery(string queryName) 02412 { 02413 string returnValue = string.Empty; 02414 string sqlString = string.Empty; 02415 02416 if (queryName.StartsWith("EXEC ")) 02417 { 02418 sqlString = queryName; 02419 } 02420 else 02421 { 02422 queryName = "[" + queryName; 02423 if (queryName.Contains("(")) 02424 queryName = queryName.Replace("(", "]("); 02425 else 02426 queryName = queryName + "]"; 02427 02428 sqlString = string.Format("SELECT * FROM {0}", queryName); 02429 } 02430 02431 using (SqlConnection sqlConnection = new SqlConnection(DataAccessUtilities.ConnectionString)) 02432 { 02433 sqlConnection.Open(); 02434 02435 using (SqlCommand sqlCommand = new SqlCommand(sqlString, sqlConnection)) 02436 { 02437 sqlCommand.CommandType = System.Data.CommandType.Text; 02438 02439 using (SqlDataReader sqlReader = sqlCommand.ExecuteReader()) 02440 { 02441 if (sqlReader.Read()) 02442 { 02443 returnValue = (sqlReader.FieldCount > 0) 02444 ? sqlReader[0].ToString() 02445 : string.Empty; 02446 } 02447 } 02448 } 02449 } 02450 02451 return returnValue; 02452 } 02453 #endregion SELECT Building Methods 02454 } 02455 }
1.7.5 
